/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     7/26/2010 23:09:07                           */
/*==============================================================*/


/*==============================================================*/
/* Table: City                                                  */
/*==============================================================*/
create table City (
   CountryId            int                  not null,
   StateId              int                  not null,
   CityId               bigint               not null,
   CityName             text                 not null,
   CityAlias            text                 null,
   CityPhoneCode        smallint             not null,
   CityZipCode          text                 not null
)
go

alter table City
   add constraint PK_CITY primary key nonclustered (CountryId, StateId, CityId)
go

/*==============================================================*/
/* Index: Cities_FK                                             */
/*==============================================================*/
create index Cities_FK on City (
CountryId ASC,
StateId ASC
)
go

/*==============================================================*/
/* Table: Contact                                               */
/*==============================================================*/
create table Contact (
   ContactId            int                  not null,
   LocationId           bigint               null,
   ContactName          text                 not null,
   Mobile               text                 null,
   Moblie2              text                 null,
   Home                 text                 null,
   Business             text                 null,
   Hotline              text                 null,
   Hotline2             text                 null,
   IM                   text                 null,
   IM2                  text                 null,
   IM3                  text                 null,
   Email                text                 null
)
go

alter table Contact
   add constraint PK_CONTACT primary key nonclustered (ContactId)
go

/*==============================================================*/
/* Index: Adress_FK                                             */
/*==============================================================*/
create index Adress_FK on Contact (
LocationId ASC
)
go

/*==============================================================*/
/* Table: Country                                               */
/*==============================================================*/
create table Country (
   CountryId            int                  not null,
   CountryName          text                 not null,
   CountryUniversalName text                 not null,
   CountryAlias         text                 null,
   CountryPhoneCode     smallint             not null
)
go

alter table Country
   add constraint PK_COUNTRY primary key nonclustered (CountryId)
go

/*==============================================================*/
/* Table: District                                              */
/*==============================================================*/
create table District (
   CountryId            int                  not null,
   StateId              int                  not null,
   CityId               bigint               not null,
   DistrictId           bigint               not null,
   DistrictName         text                 not null,
   DistrictZipCode      text                 not null
)
go

alter table District
   add constraint PK_DISTRICT primary key nonclustered (CountryId, StateId, CityId, DistrictId)
go

/*==============================================================*/
/* Index: Districts_FK                                          */
/*==============================================================*/
create index Districts_FK on District (
CountryId ASC,
StateId ASC,
CityId ASC
)
go

/*==============================================================*/
/* Table: Facility                                              */
/*==============================================================*/
create table Facility (
   FacilityId           int                  not null,
   FacilityName         text                 not null,
   FacilityDesc         text                 null
)
go

alter table Facility
   add constraint PK_FACILITY primary key nonclustered (FacilityId)
go

/*==============================================================*/
/* Table: GroupTags                                             */
/*==============================================================*/
create table GroupTags (
   TagGroupName         text                 not null,
   TagName              text                 not null
)
go

alter table GroupTags
   add constraint PK_GROUPTAGS primary key (TagGroupName, TagName)
go

/*==============================================================*/
/* Index: GroupTags_FK                                          */
/*==============================================================*/
create index GroupTags_FK on GroupTags (
TagGroupName ASC
)
go

/*==============================================================*/
/* Index: GroupTags2_FK                                         */
/*==============================================================*/
create index GroupTags2_FK on GroupTags (
TagName ASC
)
go

/*==============================================================*/
/* Table: Hotel                                                 */
/*==============================================================*/
create table Hotel (
   HotelId              int                  not null,
   HotelChainId         int                  null,
   LocationId           bigint               not null,
   HotelName            text                 not null,
   HotelUniversalName   text                 not null,
   HotelStarLevel       smallint             not null,
   HotelHomepage        text                 null
)
go

alter table Hotel
   add constraint PK_HOTEL primary key nonclustered (HotelId)
go

/*==============================================================*/
/* Index: HotelLocation_FK                                      */
/*==============================================================*/
create index HotelLocation_FK on Hotel (
LocationId ASC
)
go

/*==============================================================*/
/* Index: ChainHotels_FK                                        */
/*==============================================================*/
create index ChainHotels_FK on Hotel (
HotelChainId ASC
)
go

/*==============================================================*/
/* Table: HotelChain                                            */
/*==============================================================*/
create table HotelChain (
   HotelChainId         int                  not null,
   HotelChainName       text                 not null
)
go

alter table HotelChain
   add constraint PK_HOTELCHAIN primary key nonclustered (HotelChainId)
go

/*==============================================================*/
/* Table: HotelContacts                                         */
/*==============================================================*/
create table HotelContacts (
   HotelId              int                  not null,
   ContactId            int                  not null
)
go

alter table HotelContacts
   add constraint PK_HOTELCONTACTS primary key (HotelId, ContactId)
go

/*==============================================================*/
/* Index: HotelContacts_FK                                      */
/*==============================================================*/
create index HotelContacts_FK on HotelContacts (
HotelId ASC
)
go

/*==============================================================*/
/* Index: HotelContacts2_FK                                     */
/*==============================================================*/
create index HotelContacts2_FK on HotelContacts (
ContactId ASC
)
go

/*==============================================================*/
/* Table: HotelFacilities                                       */
/*==============================================================*/
create table HotelFacilities (
   HotelId              int                  not null,
   FacilityId           int                  not null
)
go

alter table HotelFacilities
   add constraint PK_HOTELFACILITIES primary key (HotelId, FacilityId)
go

/*==============================================================*/
/* Index: HotelFacilities_FK                                    */
/*==============================================================*/
create index HotelFacilities_FK on HotelFacilities (
HotelId ASC
)
go

/*==============================================================*/
/* Index: HotelFacilities2_FK                                   */
/*==============================================================*/
create index HotelFacilities2_FK on HotelFacilities (
FacilityId ASC
)
go

/*==============================================================*/
/* Table: HotelServices                                         */
/*==============================================================*/
create table HotelServices (
   HotelId              int                  not null,
   ServiceNo            int                  not null
)
go

alter table HotelServices
   add constraint PK_HOTELSERVICES primary key (HotelId, ServiceNo)
go

/*==============================================================*/
/* Index: HotelServices_FK                                      */
/*==============================================================*/
create index HotelServices_FK on HotelServices (
HotelId ASC
)
go

/*==============================================================*/
/* Index: HotelServices2_FK                                     */
/*==============================================================*/
create index HotelServices2_FK on HotelServices (
ServiceNo ASC
)
go

/*==============================================================*/
/* Table: Location                                              */
/*==============================================================*/
create table Location (
   LocationId           bigint               not null,
   CountryId            int                  not null,
   StateId              int                  not null,
   CityId               bigint               not null,
   DistrictId           bigint               not null,
   StreetId             bigint               not null,
   LocationName         text                 null,
   LocationDesc         text                 null
)
go

alter table Location
   add constraint PK_LOCATION primary key nonclustered (LocationId)
go

/*==============================================================*/
/* Index: LocationStreet_FK                                     */
/*==============================================================*/
create index LocationStreet_FK on Location (
CountryId ASC,
StateId ASC,
CityId ASC,
DistrictId ASC,
StreetId ASC
)
go

/*==============================================================*/
/* Table: Room                                                  */
/*==============================================================*/
create table Room (
   HotelId              int                  not null,
   RoomNo               int                  not null,
   RoomPeople           smallint             not null,
   RoomType             int                  not null,
   RoomAttributes       bit                  not null,
   RoomFacilities       bit                  not null,
   RoomServices         int                  not null
)
go

alter table Room
   add constraint PK_ROOM primary key nonclustered (HotelId, RoomNo)
go

/*==============================================================*/
/* Index: HotelRooms_FK                                         */
/*==============================================================*/
create index HotelRooms_FK on Room (
HotelId ASC
)
go

/*==============================================================*/
/* Table: RoomFacilities                                        */
/*==============================================================*/
create table RoomFacilities (
   FacilityId           int                  not null,
   HotelId              int                  not null,
   RoomNo               int                  not null
)
go

alter table RoomFacilities
   add constraint PK_ROOMFACILITIES primary key (HotelId, FacilityId, RoomNo)
go

/*==============================================================*/
/* Index: RoomFacilities_FK                                     */
/*==============================================================*/
create index RoomFacilities_FK on RoomFacilities (
FacilityId ASC
)
go

/*==============================================================*/
/* Index: RoomFacilities2_FK                                    */
/*==============================================================*/
create index RoomFacilities2_FK on RoomFacilities (
HotelId ASC,
RoomNo ASC
)
go

/*==============================================================*/
/* Table: RoomServices                                          */
/*==============================================================*/
create table RoomServices (
   HotelId              int                  not null,
   RoomNo               int                  not null,
   ServiceNo            int                  not null
)
go

alter table RoomServices
   add constraint PK_ROOMSERVICES primary key (HotelId, RoomNo, ServiceNo)
go

/*==============================================================*/
/* Index: RoomServices_FK                                       */
/*==============================================================*/
create index RoomServices_FK on RoomServices (
HotelId ASC,
RoomNo ASC
)
go

/*==============================================================*/
/* Index: RoomServices2_FK                                      */
/*==============================================================*/
create index RoomServices2_FK on RoomServices (
ServiceNo ASC
)
go

/*==============================================================*/
/* Table: RoomTags                                              */
/*==============================================================*/
create table RoomTags (
   HotelId              int                  not null,
   RoomNo               int                  not null,
   TagName              text                 not null
)
go

alter table RoomTags
   add constraint PK_ROOMTAGS primary key (HotelId, RoomNo, TagName)
go

/*==============================================================*/
/* Index: RoomTags_FK                                           */
/*==============================================================*/
create index RoomTags_FK on RoomTags (
HotelId ASC,
RoomNo ASC
)
go

/*==============================================================*/
/* Index: RoomTags2_FK                                          */
/*==============================================================*/
create index RoomTags2_FK on RoomTags (
TagName ASC
)
go

/*==============================================================*/
/* Table: Service                                               */
/*==============================================================*/
create table Service (
   ServiceNo            int                  not null,
   ServiceName          text                 not null,
   ServiceShortName     text                 null,
   ServiceDesp          text                 not null
)
go

alter table Service
   add constraint PK_SERVICE primary key nonclustered (ServiceNo)
go

/*==============================================================*/
/* Table: State                                                 */
/*==============================================================*/
create table State (
   CountryId            int                  not null,
   StateId              int                  not null,
   StateName            text                 not null,
   StateAlias           text                 null,
   StateZipCode         text                 not null
)
go

alter table State
   add constraint PK_STATE primary key nonclustered (CountryId, StateId)
go

/*==============================================================*/
/* Index: States_FK                                             */
/*==============================================================*/
create index States_FK on State (
CountryId ASC
)
go

/*==============================================================*/
/* Table: Street                                                */
/*==============================================================*/
create table Street (
   CountryId            int                  not null,
   StateId              int                  not null,
   CityId               bigint               not null,
   DistrictId           bigint               not null,
   StreetId             bigint               not null,
   StreetName           text                 not null
)
go

alter table Street
   add constraint PK_STREET primary key nonclustered (CountryId, StateId, CityId, DistrictId, StreetId)
go

/*==============================================================*/
/* Index: Streets_FK                                            */
/*==============================================================*/
create index Streets_FK on Street (
CountryId ASC,
StateId ASC,
CityId ASC,
DistrictId ASC
)
go

/*==============================================================*/
/* Table: Tag                                                   */
/*==============================================================*/
create table Tag (
   TagName              text                 not null,
   TagDesc              text                 null
)
go

alter table Tag
   add constraint PK_TAG primary key nonclustered (TagName)
go

/*==============================================================*/
/* Table: TagGroup                                              */
/*==============================================================*/
create table TagGroup (
   TagGroupName         text                 not null,
   TagGroupDesc         text                 null
)
go

alter table TagGroup
   add constraint PK_TAGGROUP primary key nonclustered (TagGroupName)
go

